﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Daneas.Utility.Universals;

namespace Daneas.Utility.Data
{
    /// <summary>
    /// MSSQL方法类
    /// </summary>
    public class DBHelper
    {
        public static string connString = PubConstant.ConnectionString;

        public static int timeOut = PubConstant.TimeOut <= 0 ? 20 : PubConstant.TimeOut;

        #region 公用方法

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";

            object res = ExecuteScalar(sql);

            if (res == null || res == DBNull.Value)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 获取字段的最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ") as MaxValue from " + TableName;

            object obj = ExecuteScalar(strsql);

            if (obj == null || obj == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";

            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = ExecuteScalar(strsql);

            if (obj == null || obj == DBNull.Value)
            {
                return false;
            }

            return Convert.ToInt32(obj) > 0;
        }

        /// <summary>
        /// 是否存在对象
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = ExecuteNonQuery(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString()); //也可能=0
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 带参查询是否存在对象
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = ExecuteNonQuery(strSql, cmdParms);

            int cmdresult;

            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static int GetCount(string sql)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString());

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        public static int GetCount(string sql, SqlParameter param)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString(), param);

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        public static int GetCount(string sql, SqlParameter[] paramList)
        {
            StringBuilder sb_count = new StringBuilder();

            sb_count.Append("SELECT COUNT(*) FROM (");
            sb_count.Append(sql);
            sb_count.Append(" ) T");

            object obj = ExecuteScalar(sb_count.ToString(), paramList);

            if (obj == DBNull.Value || obj == null)
            {
                return 0;
            }

            return Convert.ToInt32(obj);
        }

        #endregion

        /// <summary>
        /// 处理连接(非事务)
        /// </summary>
        public static void SqlPrepare(SqlConnection conn, SqlCommand cmd, string sql, SqlParameter[] paramList)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
            }


            if (cmd != null)
            {
                cmd.Dispose();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.CommandTimeout = timeOut;
                cmd.Parameters.Clear();
            }

            if (paramList != null)
            {
                foreach (SqlParameter param in paramList)
                {
                    if (param.Value == null)
                    {
                        param.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(param);
                }
            }
        }

        public static SqlCommand TranCmdPrepare(SqlCommand cmd, string sql, SqlParameter[] paramList)
        {
            if (cmd != null)
            {
                cmd.Parameters.Clear();

                cmd.CommandText = sql;

                cmd.CommandType = CommandType.Text;

                if (paramList != null && paramList.Length > 0)
                {
                    foreach (SqlParameter param in paramList)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                return cmd;
            }

            return null;
        }

        public static SqlConnection GetConn(SqlConnection conn)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
                return conn;
            }

            return conn;
        }

        #region 增删改

        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, null);

                try
                {
                    int var = cmd.ExecuteNonQuery();

                    return var;
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static int ExecuteNonQuery(string sql, SqlCommand cmd)
        {
            try
            {
                cmd = TranCmdPrepare(cmd, sql, null);

                int var = cmd.ExecuteNonQuery();

                return var;
            }
            catch
            {
                throw new Exception();
            }
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] paramList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, paramList);

                try
                {
                    int var = cmd.ExecuteNonQuery();

                    return var;
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static int ExecuteNonQuery(string sql, SqlParameter param)
        {
            SqlParameter[] paramList = { param };

            return ExecuteNonQuery(sql, paramList);
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] paramList, SqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, paramList);

            int var = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();

            return var;
        }

        public static int ExecuteNonQuery(string sql, SqlParameter param, SqlCommand cmd)
        {
            SqlParameter[] paramList = { param };

            return ExecuteNonQuery(sql, paramList, cmd);
        }

        #endregion

        #region 查一个object

        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, null);

                try
                {
                    object val = cmd.ExecuteScalar();

                    return val;
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static object ExecuteScalar(string sql, SqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, null);

            object val = cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            return val;
        }

        public static object ExecuteScalar(string sql, SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, paras);

                try
                {
                    object val = cmd.ExecuteScalar();

                    return val;
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static object ExecuteScalar(string sql, SqlParameter param)
        {
            SqlParameter[] paramList = { param };

            return ExecuteScalar(sql, paramList);
        }

        public static object ExecuteScalar(string sql, SqlParameter[] paras, SqlCommand cmd)
        {
            cmd = TranCmdPrepare(cmd, sql, paras);

            object val = cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            return val;
        }

        public static object ExecuteScalar(string sql, SqlParameter param, SqlCommand cmd)
        {
            SqlParameter[] paramList = { param };

            return ExecuteScalar(sql, paramList, cmd);
        }

        #endregion

        #region 查DataSet

        public static DataSet ExecutQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                DataSet ds = new DataSet();

                if (conn.State != ConnectionState.Open)
                {
                    conn.Close();
                    conn.Open();
                }

                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

                try
                {
                    adapter.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }

                return ds;
            }
        }

        public static DataSet ExecutQuery(string sql, SqlCommand cmd)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.CommandText = sql;

                adapter.Fill(ds, "ds");

                adapter.SelectCommand.Parameters.Clear();
                adapter.Dispose();

                cmd.Parameters.Clear();

                return ds;
            }
            catch
            {
                throw new Exception();
            }
        }

        public static DataSet ExecutQuery(string sql, SqlParameter param)
        {
            SqlParameter[] paramList = { param };

            return ExecutQuery(sql, paramList);
        }

        public static DataSet ExecutQuery(string sql, SqlParameter[] paramList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                DataSet ds = new DataSet();

                if (conn.State != ConnectionState.Open)
                {
                    conn.Close();
                    conn.Open();
                }

                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

                try
                {
                    if (paramList != null && paramList.Length > 0)
                    {
                        foreach (SqlParameter param in paramList)
                        {
                            adapter.SelectCommand.Parameters.Add(param);
                        }
                    }

                    adapter.Fill(ds, "ds");

                    return ds;
                }
                catch
                {
                    throw new Exception();
                }
                finally
                {
                    adapter.Dispose();
                }
            }
        }

        public static DataSet ExecutQuery(string sql, SqlParameter param, SqlCommand cmd)
        {
            SqlParameter[] paramList = { param };

            return ExecutQuery(sql, paramList, cmd);
        }

        public static DataSet ExecutQuery(string sql, SqlParameter[] paramList, SqlCommand cmd)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.SelectCommand.CommandText = sql;

                if (paramList != null && paramList.Length > 0)
                {
                    foreach (SqlParameter param in paramList)
                    {
                        adapter.SelectCommand.Parameters.Add(param);
                    }
                }


                adapter.Fill(ds, "ds");

                adapter.SelectCommand.Parameters.Clear();
                adapter.Dispose();

                cmd.Parameters.Clear();

                return ds;
            }
            catch 
            {
                throw new Exception();
            }
        }

        #endregion

        #region 查SqlDataReader

        public static SqlDataReader ExecuteGetReader(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, null);

                cmd.Connection = conn;

                try
                {
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    return reader;  //注意不能关闭连接，否则调用方无法读取数据。
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static SqlDataReader ExecuteGetReader(string sql, SqlCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                cmd = TranCmdPrepare(cmd, sql, null);

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return reader;  //注意不能关闭连接，否则调用方无法读取数据。
            }
        }

        public static SqlDataReader ExecuteGetReader(string sql, SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();

                SqlPrepare(conn, cmd, sql, paras);

                try
                {
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    return reader;  //注意不能关闭连接，否则调用方无法读取数据。
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        public static SqlDataReader ExecuteGetReader(string sql, SqlParameter param)
        {
            SqlParameter[] paramList = { param };

            return ExecuteGetReader(sql, paramList);
        }

        public static SqlDataReader ExecuteGetReader(string sql, SqlParameter[] paras, SqlCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                cmd = TranCmdPrepare(cmd, sql, paras);

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return reader;  //注意不能关闭连接，否则调用方无法读取数据。
            }
        }

        public static SqlDataReader ExecuteGetReader(string sql, SqlParameter param, SqlCommand cmd)
        {
            SqlParameter[] paramList = { param };

            return ExecuteGetReader(sql, paramList, cmd);
        }

        #endregion

        #region 分页查询

        public static DataTable QueryPage(string sql, string orderByStr, int pageIndex, int pageSize, out int totalCount)
        {
            List<SqlParameter> param = new List<SqlParameter>();

            return QueryPage(sql, orderByStr, param.ToArray(), pageIndex, pageSize, out totalCount);
        }


        public static DataTable QueryPage(string sql, string orderByStr, SqlParameter param, int pageIndex, int pageSize, out int totalCount)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();
            paramList.Add(param);

            return QueryPage(sql, orderByStr, paramList.ToArray(), pageIndex, pageSize, out totalCount);
        }


        public static DataTable QueryPage(string sql, string orderByStr, SqlParameter[] paramList, int pageIndex, int pageSize, out int totalCount)
        {
            if (pageIndex <= 0 || pageSize <= 0)
            {
                pageIndex = 1;
                pageSize = 50;
            }

            int start = pageSize * (pageIndex - 1) + 1;
            int end = pageIndex * pageSize;

            string exSql = string.Format(@" SELECT * FROM
                                (
	                                SELECT ROW_NUMBER() OVER ({0}) AS ROWNUM ,* FROM 
	                                (
		                                {1}
	                                ) AS T
                                ) AS PAGETEMP
                                WHERE PAGETEMP.ROWNUM BETWEEN " + start + " AND " + end, orderByStr, sql);

            DataTable dt = ExecutQuery(exSql, paramList).Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                totalCount = 0;
                return new DataTable();
            }

            totalCount = GetCount(sql, DeepCloneSqlParameters(paramList));

            return dt;

            #region 貌似有问题的哦
            //using (SqlConnection conn = new SqlConnection(connString))
            //{
            //    DataSet ds = new DataSet();

            //    SqlPrepare(conn, null, paramList);

            //    SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

            //    adapter.SelectCommand.CommandTimeout = timeOut;

            //    try
            //    {
            //        if (paramList != null)
            //        {
            //            adapter.SelectCommand.Parameters.AddRange(paramList);
            //        }

            //        adapter.Fill(ds, start, end, "dataTable");

            //        adapter.SelectCommand.Parameters.Clear();

            //        SqlParameter[] newParams = (SqlParameter[])paramList.Clone();

            //        totalCount = GetCount(sql, newParams); 

            //        return ds.Tables[0];
            //    }
            //    catch (System.Data.SqlClient.SqlException ex)
            //    {
            //        throw new Exception(ex.Message);
            //    }
            //    finally
            //    {
            //        adapter.Dispose();

            //        conn.Close();
            //    }
            //}
            #endregion
        }

        public static DataTable QueryPage(string sql, List<SortParam> sorts, SqlParameter[] paramList, int pageIndex, int pageSize, out int total)
        {
            if (sorts == null)
            {
                total = 0;
                return null;
            }

            if (pageIndex <= 0 || pageSize <= 0)
            {
                pageIndex = 1;
                pageSize = 50;
            }

            int start = pageSize * (pageIndex - 1) + 1;
            int end = pageIndex * pageSize;

            string orderByStr = " order by ";
            foreach (SortParam sort in sorts)
            {
                orderByStr += sort.SortColunm + " " + sort.SortType.ToString() + ",";
            }
            orderByStr = orderByStr.TrimEnd(',');

            string exSql = string.Format(@" SELECT * FROM
                                (
	                                SELECT ROW_NUMBER() OVER ({0}) AS ROWNUM ,* FROM 
	                                (
		                                {1}
	                                ) AS T
                                ) AS PAGETEMP
                                WHERE PAGETEMP.ROWNUM BETWEEN " + start + " AND " + end, orderByStr, sql);

            DataTable dt = ExecutQuery(exSql, paramList).Tables[0];

            if (dt == null || dt.Rows.Count <= 0)
            {
                total = 0;
                return null;
            }

            total = GetCount(sql, DeepCloneSqlParameters(paramList));

            return dt;

        }

        #endregion

        /// <summary>
        /// 执行事务sql
        /// </summary>
        /// <param name="SQLStringList"></param>
        /// <returns></returns>
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Open();
                }

                SqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                try
                {
                    int count = 0;

                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];

                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;

                            count += cmd.ExecuteNonQuery();
                        }
                    }

                    tran.Commit();

                    return count;
                }
                catch
                {
                    tran.Rollback();

                    return -1;
                }
            }
        }

        /// <summary>
        /// 插入图像
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="fs"></param>
        /// <returns></returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Close();
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand(strSQL, conn);

                SqlParameter myParameter = new SqlParameter("@fs", SqlDbType.Image);

                myParameter.Value = fs;

                cmd.Parameters.Add(myParameter);

                try
                {

                    int rows = cmd.ExecuteNonQuery();

                    return rows;
                }
                catch
                {
                    throw new Exception();
                }
            }
        }

        /// <param name="tableName">要批量写入的表</param>
        /// <param name="columnMappings">自定义的datatable和数据库的字段的映射</param>
        /// <param name="dataTable">数据集</param>
        /// <returns></returns>
        public static bool ExecuteBySqlBulkCopy(string tableName, Dictionary<string, string> columnMappings, DataTable dataTable, out string errorMessage)
        {
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Close();
                    conn.Open();
                }

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn);

                try
                {
                    sqlBulkCopy.DestinationTableName = tableName;

                    foreach (string key in columnMappings.Keys)
                    {
                        sqlBulkCopy.ColumnMappings.Add(key, columnMappings[key]);
                    }

                    //conn.Open();已经打开SqlPrepare(conn,null,null);
                    sqlBulkCopy.WriteToServer(dataTable);
                }
                catch (Exception e)
                {
                    errorMessage = e.Message;
                }
                finally
                {
                    sqlBulkCopy.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// 复制sqlparameters
        /// </summary>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static SqlParameter[] DeepCloneSqlParameters(SqlParameter[] paramList)
        {
            if (paramList == null || paramList.Length <= 0)
            {
                return new SqlParameter[] { };
            }

            List<SqlParameter> temp = new List<SqlParameter>();

            foreach (SqlParameter param in paramList)
            {
                SqlParameter p = new SqlParameter();
                p.SqlDbType = param.SqlDbType;
                p.ParameterName = param.ParameterName;
                p.Value = param.Value;
                p.TypeName = param.TypeName;
                p.UdtTypeName = param.UdtTypeName;
                p.SqlValue = param.SqlValue;

                temp.Add(p);
            }

            return temp.ToArray();
        }

        /// <summary>
        /// 生成sqlparam
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static SqlParameter InitSqlParam(string name, object value, SqlDbType t)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = name;
            param.SqlValue = value;
            param.Value = value;
            param.SqlDbType = t;
            return param;
        }

        /// <summary>
        /// 直接根据传递值申城sqlparameter
        /// </summary>
        /// <param name="name">键值名称</param>
        /// <param name="value">键值</param>
        /// <returns></returns>
        public static SqlParameter InitSqlParam(string name, object value)
        {
            Type type = value.GetType();

            SqlParameter param = new SqlParameter();
            param.ParameterName = name;
            param.SqlValue = value;
            param.Value = value;
            param.SqlDbType = Convers.NetType2SqlType(type);
            return param;
        }
    }

    /// <summary>
    /// 数据分页排序
    /// </summary>
    public class SortParam
    {
        public SortParam()
        {
            this.SortType = SortType.DESC;
        }

        public SortParam(SortType sorttype)
        {
            this.SortType = sorttype;
        }

        public string SortColunm { get; set; }

        public SortType SortType { get; set; }
    }

    /// <summary>
    /// 数据排序
    /// </summary>
    public enum SortType
    {
        /// <summary>
        /// 降序
        /// </summary>
        DESC = 0,

        /// <summary>
        /// 升序
        /// </summary>
        ASC = 1
    }
}
